from dbutils.pooled_db import PooledDB
from threading import Thread
from application.config.mysql import db_config
import pymysql

pool = PooledDB(
    creator=pymysql,
    host=db_config['host'],
    port=db_config['port'],
    user=db_config['user'],
    password=db_config['password'],
    database=db_config['database'],
    charset='utf8mb4',  # 字符编码
    ping=0,
    blocking=True,  # 没有链接等待
    autocommit=True,  # 无需手动提交事务
    maxconnections=3000,
    mincached=1,  # 初始化时连接池中的初始连接数
    maxcached=3000,  # 最多允许的闲置连接数
    maxshared=3000,  # 最多允许的共享连接数
    maxusage=None,  # 一个连接最多被重复使用的次数，None 表示无限制
)


# 查列表
def fetchall(sql, *args):
    conn = pool.connection()  # 获取连接
    cursor = conn.cursor()  # 创建游标
    cursor.execute(sql, args)
    result = cursor.fetchall()
    cursor.close()  # 关闭游标
    conn.close()  # 连接放回连接池
    return result


# 查详情
def fetchone(sql, *args):
    conn = pool.connection()  # 获取连接
    cursor = conn.cursor()  # 创建游标
    cursor.execute(sql, args)
    result = cursor.fetchone()
    cursor.close()  # 关闭游标
    conn.close()  # 连接放回连接池
    return result


def put_sql(sql,*args):
    conn = pool.connection()  # 获取连接
    cursor = conn.cursor()  # 创建游标
    try:
        cursor.execute(sql, args)
        conn.commit()  # 提交事务
    except Exception as e:
        conn.rollback()  # 出现异常时回滚事务
        raise e
    finally:
        cursor.close()  # 关闭游标
        conn.close()  # 连接放回连接池


# 模拟多线程并发
def bfa():
    sql = 'INSERT INTO frontapp_answer_test (openid,answer_sum) VALUES (%s,%s);'
    for i in range(10000):
        t = Thread(target=fetchone, args=(sql, i, i))
        t.start()


if __name__ == "__main__":
    bfa()
